from openpyxl import Workbook
import pathlib
import os
import sys
import easygui
import sqlite3
import xlwt
import configparser
from inc import *
root=pathlib.Path(os.path.dirname(os.path.realpath(sys.argv[0])))
db=sqlite3.connect(str(root/"yj.db"))
cur = db.cursor()

alias={}
alias_={}
for i in conf.items("alias") :
    alias[i[0]]=i[1]
    alias_[i[1]]=i[0]
wb=Workbook()
ws=wb.active


grade=cur.execute("select nj from main group by nj")
########################### 考场人数统计
'''
ws.cell(1,1,"考场")

result=cur.execute("select kc from main group by kc")
result=result.fetchall()
grade=cur.execute("select nj from main group by nj")
grade=grade.fetchall()
row=1
col=1
ws.cell(row,col,"考场")
for i in grade:
    col=col+1
    ws.cell(row,col,dx[int(i[0])]+"年级")
for kch in result:
    row=row+1
    col=1
    ws.cell(row,col,getAlias(kch[0]))
    for njh in grade:
        col=col+1
        count=cur.execute("select count(*) from main where kc='{0}' and nj='{1}'".format(kch[0],njh[0]))
        count=count.fetchone()
        ws.cell(row,col,count[0])

wb.save(root/"output/考生数.xlsx")
print(root/"output/考生数.xlsx")
wb=None
ws=None

'''
###########################
def createxl(grade): #生成导入数据
    workbook = xlwt.Workbook(encoding = 'utf-8')
    worksheet = workbook.add_sheet('sheet1')

    # str=["考生号","准考证号","姓名","考场号","座位号","班级"]
    str=["考生号","准考证号","姓名","年级","班级","考场号","座位号"]
    njs_={7:"七年级",8:"八年级",9:"九年级"}
    n=0
    for i in str:
        worksheet.write(0,n,i)
        n=n+1
    sql='select id,kh,name,nj,bj,kc,i from main where nj="{0}"'.format(grade)
    result=cur.execute(sql)
    result=result.fetchall()
    row=1
    for v in result:
        worksheet.write(row,0,v[0])
        worksheet.write(row,1,v[1])
        worksheet.write(row,2,v[2])
        worksheet.write(row,3,njs_[int(v[3])])
        worksheet.write(row,4,f"{getAlias(v[4][2:])}班")
        worksheet.write(row,5,v[5])
        worksheet.write(row,6,v[6])

        
        row=row+1

    workbook.save(root/"output/{0}年级考场名单.xls".format(grade))
    print(root/"output/{0}年级考场名单.xls".format(grade))

result=cur.execute("select nj from main group by nj")
result=result.fetchall()
for v in result:#生成导入数据
    createxl(v[0])

################################

def addrow(ws,data,row,i):
    if(len(data)<=i):
        return row  
    else:
        gradeStr={'1':"一年级",'2':"二年级",'3':"三年级",'4':"四年级",'5':"五年级",'6':"六年级",'7':"七年级",'8':"八年级",'9':"九年级"}
        gradeStr=gradeStr[data[i][3]]
        ws.cell(row,1,data[i][0])
        ws.cell(row,2,"{0}考场".format(getAlias(data[i][1])))
        ws.cell(row,3,"{0}班".format(getAlias(data[i][2][2:])))
        ws.cell(row,4,gradeStr)
        ws.cell(row,5,data[i][4])
        row=row+1
        return row


def addinfo(ws,list,groups,row):
    global wb
    if(len(list)<((groups+1)*2)): #单列
        for i in range(0,len(list[groups*2])):
            row=addrow(ws,list[groups*2],row,i)
    else: #双列
        list0=list[groups*2]
        list1=list[groups*2+1]
        if(len(list1)>=len(list0)):#获取人数较多的一个小组人数
            len_=len(list1)
        else:
            len_=len(list0)
        for i in range(0,len_) :
            try:
                row=addrow(ws,list0,row,i)
                row=addrow(ws,list1,row,i)
            except:
                print(ws.title)
                print(row ,i,len(list))
                print(len(list),((groups+1)*2),groups)
                for i in range(0,int((len(list)+1)/2)):
                    print(i)
                print("error,break")
                wb.remove(wb["Sheet"])   
                wb.save(root/'output/条码信息1.xlsx')
                exit()
    return row
####导出条码信息
result=cur.execute("select kc from main group by kc order by kc asc")
result=result.fetchall()
wb=Workbook()  
for kc in result:
    kc=kc[0]
    ws=wb.create_sheet("{0}考场".format(getAlias(kc)))
    str=["考号","考场","班级","年级","姓名"]
    row=2
    col=1
    for v in str:
        ws.cell(1,col,v)
        col=col+1
    result=cur.execute("select z from main where kc='{0}' group by z order by z asc".format(getClassNo(kc)))
    result=result.fetchall()
    list=[] #考场信息
    for z in result:
        z=z[0]
        result=cur.execute("select kh,kc,bj,nj,name from main where kc='{0}' and z='{1}' order by p asc".format(getClassNo(kc),z))
        list.append(result.fetchall())
    #print(list)
    for i in range(0,int((len(list)+1)/2)):
        row = addinfo(ws,list,i,row)
wb.remove(wb["Sheet"])   
wb.save(root/'output/条码信息.xlsx')
print(root/'output/条码信息.xlsx')

#  导出单个年级条码信息
result = cur.execute("select nj from main group by nj order by nj asc")
nj=result.fetchall()
gradeStr={'1':"一年级",'2':"二年级",'3':"三年级",'4':"四年级",'5':"五年级",'6':"六年级",'7':"七年级",'8':"八年级",'9':"九年级"}
for inj in nj:
    result=cur.execute("select kc from main group by kc order by kc asc")
    result=result.fetchall()
    wb=Workbook() 
    for kc in result:
        kc=kc[0]
        ws=wb.create_sheet("{0}考场".format(getAlias(kc)))
        str=["考号","考场","班级","年级","姓名"]
        row=2
        col=1
        for v in str:
            ws.cell(1,col,v)
            col=col+1
        result=cur.execute("select kh,kc,bj,nj,name from main where kc='{0}' and  nj ='{1}' order by z,p asc".format(getClassNo(kc),inj[0]))
        for i in result.fetchall():
            ws.cell(row,1,i[0])
            ws.cell(row,2,"{0}考场".format(getAlias(i[1])))
            ws.cell(row,3,"{0}班".format(getAlias(i[2][2:])))
            ws.cell(row,4,gradeStr[i[3]])
            ws.cell(row,5,i[4])
            row=row+1
    wb.remove(wb["Sheet"])   
    wb.save(root/'output/{0}条码信息.xlsx'.format(gradeStr[inj[0]]))
    print(root/'output/{0}条码信息.xlsx'.format(gradeStr[inj[0]]))
easygui.msgbox("已完成！")
